﻿using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Dapper;
using PMS.IRepository;
using PMS.Models.Base;
using PMS.Utils;

namespace PMS.Repository
{
    public class BaseConfigRepository : BaseRepository<BaseConfig>, IBaseConfigRepository
    {
        #region 公共方法


        public int Add(BaseConfig user)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into BaseConfig(");
            strSql.Append("RoleName,Sym,Value,Remark,Enable,IsDel,CreateBy,CreateDate,ModifyBy,ModifyDate,DeleteBy,DeleteDate)");
            strSql.Append(" values (");
            strSql.Append("@RoleName,@Sym,@Value,@Remark,@Enable,@IsDel,@CreateBy,@CreateDate,@ModifyBy,@ModifyDate,@DeleteBy,@DeleteDate)");
            strSql.Append(";select @@IDENTITY");
            using (DbConn)
            {
                return DbConn.Execute(strSql.ToString(), user);
            }
        }

        public int Edit(BaseConfig user)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update BaseConfig set ");
            strSql.Append("RoleName=@RoleName,");
            strSql.Append("Sym=@Sym,");
            strSql.Append("Value=@Value,");
            strSql.Append("Remark=@Remark,");
            strSql.Append("Enable=@Enable,");
            strSql.Append("IsDel=@IsDel,");
            strSql.Append("CreateBy=@CreateBy,");
            strSql.Append("CreateDate=@CreateDate,");
            strSql.Append("ModifyBy=@ModifyBy,");
            strSql.Append("ModifyDate=@ModifyDate,");
            strSql.Append("DeleteBy=@DeleteBy,");
            strSql.Append("DeleteDate=@DeleteDate");
            strSql.Append(" where Id=@Id");
            using (DbConn)
            {
                return DbConn.Execute(strSql.ToString(), user);
            }
        }




        public BaseConfig Dtl(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 Id,RoleName,Sym,Value,Remark,Enable,IsDel,CreateBy,CreateDate,ModifyBy,ModifyDate,DeleteBy,DeleteDate from BaseConfig ");
            strSql.Append(" where Id=@Id");
            using (DbConn)
            {
                return DbConn.QueryFirstOrDefault<BaseConfig>(strSql.ToString(), new { Id = id });
            }
        }

        public bool IsExists(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from BaseConfig");
            strSql.Append(" where Id=@Id");
            using (DbConn)
            {
                return DbConn.ExecuteScalar(strSql.ToString(), new { Id = id }).ToInt32() > 0;
            }
        }


        public int Delete(int id, string deleteBy)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("Update BaseConfig  Set ");
            strSql.Append(" IsDel=1, ");
            strSql.Append(" DeleteBy=@DeleteBy, ");
            strSql.Append(" DeleteDate=GetDate() ");
            strSql.Append(" where Id=@Id");
            using (DbConn)
            {
                return DbConn.Execute(strSql.ToString(), new { Id = id, DeleteBy = deleteBy });
            }
        }

        public int Delete(int[] ids, string deleteBy)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("Update BaseConfig  Set ");
            strSql.Append(" IsDel=1, ");
            strSql.Append(" DeleteBy=@DeleteBy, ");
            strSql.Append(" DeleteDate=GetDate() ");
            strSql.Append(" where Id in @Ids");
            using (DbConn)
            {
                return DbConn.Execute(strSql.ToString(), new { Id = ids.ToArray(), DeleteBy = deleteBy });
            }
        }

        public List<BaseConfig> GetList()
        {
            string strSql = "Select * from BaseConfig";
            using (DbConn)
            {
                return DbConn.Query<BaseConfig>(strSql).ToList();
            }
        }

        public List<BaseConfig> GetList(Hashtable ht)
        {
            StringBuilder sb = new StringBuilder();
            Dictionary<string, object> param = new Dictionary<string, object>();
            GetStrWhereAndParam(ht, sb, param);
            string strSql = "Select * from BaseConfig where 1=1 " + sb;
            using (DbConn)
            {
                return DbConn.Query<BaseConfig>(strSql, param).ToList();
            }
        }

        public int GetCount(Hashtable ht)
        {
            StringBuilder sb = new StringBuilder();
            Dictionary<string, object> param = new Dictionary<string, object>();
            GetStrWhereAndParam(ht, sb, param);
            string strSql = "Select * from BaseConfig where 1=1 " + sb;
            using (DbConn)
            {
                return DbConn.ExecuteScalar(strSql, param).ToInt32();
            }
        }


        public void GetStrWhereAndParam(Hashtable ht, StringBuilder sb, Dictionary<string, object> param)
        {
            if (ht.IsNullOrEmptyByKey("IsDel"))
            {
                sb.Append(" And IsDel=@IsDel");
                param.Add("IsDel", ht["IsDel"]);
            }
            else
            {
                sb.Append(" And IsDel=0");
            }

            if (ht.IsNullOrEmptyByKey("Enable"))
            {
                sb.Append(" And Enable=@Enable");
                param.Add("Enable", ht["Enable"]);
            }

            if (ht.IsNullOrEmptyByKey("Value"))
            {
                sb.Append(" And Value Like @Value");
                param.Add("Value", "%" + ht["Value"] + "%");
            }

            if (ht.IsNullOrEmptyByKey("Sym"))
            {
                sb.Append(" And Sym=@Sym");
                param.Add("Sym", ht["Sym"]);
            }

        }

        #endregion


        public List<BaseConfig> GetList(Hashtable ht, string orderFiled, string sort, int pageIndex, int pageSize, out int counts)
        {

            StringBuilder strWhere = new StringBuilder();
            Dictionary<string, object> param = new Dictionary<string, object>();

            GetStrWhereAndParam(ht, strWhere, param);

            StringBuilder strSql = new StringBuilder();
            strSql.Append(" Select * from ( ");
            strSql.Append(" Select *, ");
            strSql.Append("Row_Number() over( order by ");
            strSql.Append(orderFiled);
            strSql.Append(" ");
            strSql.Append(sort);
            strSql.Append(" ) rowNum from BaseConfig Where IsDel<1 ");
            strSql.Append(strWhere);
            strSql.Append(") as res");
            strSql.Append(" Where rowNum BETWEEN ");
            strSql.Append((pageIndex - 1) * pageSize + 1);
            strSql.Append(" And ");
            strSql.Append(pageIndex * pageSize);

            using (DbConn)
            {
                string countSql = "Select Count(1) from BaseConfig  where IsDel<1 " + strWhere;
                counts = DbConn.ExecuteScalar(countSql, param).ToInt32();
                return DbConn.Query<BaseConfig>(strSql.ToString(), param).ToList();
            }


        }
    }
}